package analyticalSysetm;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import S2.server.ConnectionManage;

public class analyticalSysetm {
	
	private static Connection conn = ConnectionManage.getInstance()
			.getConnection();
	private static  String[] cido;
	private static int[] numOfOrders;
	private static int[] totalprice;
	public static void updateClientImportance() throws SQLException{
		int cnt=0;
		int []sekhem = null;
		int maxClient = 0;
		String sql = "SELECT `customer_id`,COUNT(*),SUM(`price`) FROM `order` GROUP BY `customer_id`";
		ResultSet rs = null;
		int i=0;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			rs = stmt.executeQuery();
			while(rs.next()){
				cnt++;
			}
		

		
		cido=new String[cnt];
		numOfOrders=new int[cnt];
		totalprice=new int[cnt];
		sekhem=new int[cnt];
		
		/*int j;
		int tmp1;
		String tmp2;*/
		rs = stmt.executeQuery();
		while(rs.next()){
			cido[i]=rs.getString(1);
			numOfOrders[i]=rs.getInt(2);
			totalprice[i]=rs.getInt(3);
			i++;
		}
		for(i=0;i<cnt;i++)
		{
			sekhem[i]=(int) (numOfOrders[i]*0.5+totalprice[i]*0.5);
		}
		maxClient=sekhem[0];
		for(i=0;i<cnt;i++)
		{
			if(sekhem[i]>maxClient)
				maxClient=sekhem[i];
		}
		}catch (SQLException e) {
			
			e.printStackTrace();
		}
		finally {
			if (rs != null) {
				rs.close();
			}
		}
		rs = null;
		float rate;
		int realRate = 0;
		String sql2;
		PreparedStatement ps=null;
		try{
		for(i=0;i<cnt;i++)
		{
			rate=((float)sekhem[i]/(float)maxClient)*10;
			realRate=(int)rate;
			
			ps = (PreparedStatement) conn.prepareStatement("UPDATE customer SET `rate`=? WHERE `id`=?;");
			ps.setInt(1,realRate);
			ps.setString(2, cido[i]);
			ps.executeUpdate();
		}	
		 }catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (rs != null) {
				rs.close();
			}
		}
		
	}
public static void updateInterestLevel() throws SQLException{
	float interestLevel;
	int realInterestLevel;
	//SELECT `id`,COUNT(*) FROM contact_customer WHERE `appeal`=TRUE GROUP BY `id`
		String sql = "SELECT `customer_id`,COUNT(*) FROM contact_customer WHERE appeal=TRUE GROUP BY `customer_id`";
		//SELECT `id`,COUNT(*) FROM contact_customer WHERE `appeal`=FALSE GROUP BY `id`
		String sql2 = "SELECT `customer_id`,COUNT(*) FROM contact_customer WHERE `appeal`=FALSE GROUP BY `customer_id`";
	ResultSet rs = null;
	ResultSet rs2 = null;
	int cnt = 0;
	PreparedStatement stmt = null ;
	try {
		stmt = conn.prepareStatement(sql);
		rs = stmt.executeQuery();
		
		while(rs.next()){
			cnt++;
		}
		//rs2 = stmt.executeQuery();
	PreparedStatement stmt2 = conn.prepareStatement(sql2);
	
		rs2 = stmt2.executeQuery();
	} 
	catch (SQLException e) {
		
		e.printStackTrace();
	}
	
	String[] cid=new String[cnt];
	int[] numOfAccepts=new int[cnt];
	int[] numOfDenies=new int[cnt];
	int i=0;
	rs = stmt.executeQuery();
	while(rs.next()){
		cid[i]=rs.getString(1);
		numOfAccepts[i]=rs.getInt(2);
		i++;
	}
	String tcid=null;
	while(rs2.next()){
		tcid=rs2.getString(1);
		for(i=0;i<cnt;i++){
			if(cid[i].compareTo(tcid)==0){
				numOfDenies[i]= rs2.getInt(2);
				break;
			}
		}
	}
	try {
		for(i=0;i<cnt;i++)
		{
			sql = "UPDATE customer SET interest_level=? WHERE id=?";
			stmt = conn.prepareStatement(sql);
			interestLevel=(int)(((float)numOfAccepts[i]/(float)(numOfAccepts[i]+numOfDenies[i]) )*10);
			realInterestLevel=(int)interestLevel;
			stmt.setInt(1,realInterestLevel);
			stmt.setString(2, cido[i]);
			stmt.executeUpdate();
		}	
	}
		catch (SQLException e) {
			e.printStackTrace();
		}
	finally {
		if (rs != null) {
			rs.close();
			
		}
		if(rs2!=null){
			rs2.close();
		}
		
	}
}
}

